using Android.Content;
using Android.Database.Sqlite;
using AF.Models;

namespace AF.Data
{
    /// <summary>
    /// SQLite数据库助手类
    /// </summary>
    public class DatabaseHelper : SQLiteOpenHelper
    {
        private new const string DatabaseName = "StudioFinance.db";
        private const int DatabaseVersion = 2;

        // 表名
        private const string TableCategories = "Categories";
        private const string TableTransactions = "Transactions";
        private const string TableProjects = "Projects";

        public DatabaseHelper(Context context) 
            : base(context, DatabaseName, null, DatabaseVersion)
        {
        }

        public override void OnCreate(SQLiteDatabase? db)
        {
            if (db == null) return;

            // 创建分类表
            string createCategoriesTable = @"
                CREATE TABLE " + TableCategories + @" (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Type INTEGER NOT NULL,
                    Icon TEXT,
                    Color TEXT,
                    IsDefault INTEGER DEFAULT 0,
                    CreatedAt TEXT NOT NULL
                )";
            db.ExecSQL(createCategoriesTable);

            // 创建项目表
            string createProjectsTable = @"
                CREATE TABLE " + TableProjects + @" (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Description TEXT,
                    ClientName TEXT,
                    Budget REAL,
                    Status INTEGER DEFAULT 0,
                    StartDate TEXT NOT NULL,
                    EndDate TEXT,
                    CreatedAt TEXT NOT NULL,
                    UpdatedAt TEXT,
                    Remark TEXT
                )";
            db.ExecSQL(createProjectsTable);

            // 创建交易记录表
            string createTransactionsTable = @"
                CREATE TABLE " + TableTransactions + @" (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Type INTEGER NOT NULL,
                    Amount REAL NOT NULL,
                    CategoryId INTEGER NOT NULL,
                    CategoryName TEXT NOT NULL,
                    ProjectId INTEGER,
                    ProjectName TEXT,
                    Description TEXT,
                    Date TEXT NOT NULL,
                    CreatedAt TEXT NOT NULL,
                    UpdatedAt TEXT,
                    Remark TEXT,
                    FOREIGN KEY(CategoryId) REFERENCES " + TableCategories + @"(Id),
                    FOREIGN KEY(ProjectId) REFERENCES " + TableProjects + @"(Id)
                )";
            db.ExecSQL(createTransactionsTable);

            // 插入默认分类
            InsertDefaultCategories(db);
        }

        public override void OnUpgrade(SQLiteDatabase? db, int oldVersion, int newVersion)
        {
            if (db == null) return;

            if (oldVersion < 2)
            {
                // 添加Projects表
                string createProjectsTable = @"
                    CREATE TABLE " + TableProjects + @" (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Description TEXT,
                        ClientName TEXT,
                        Budget REAL,
                        Status INTEGER DEFAULT 0,
                        StartDate TEXT NOT NULL,
                        EndDate TEXT,
                        CreatedAt TEXT NOT NULL,
                        UpdatedAt TEXT,
                        Remark TEXT
                    )";
                db.ExecSQL(createProjectsTable);

                // 为Transactions表添加项目关联字段
                db.ExecSQL("ALTER TABLE " + TableTransactions + " ADD COLUMN ProjectId INTEGER");
                db.ExecSQL("ALTER TABLE " + TableTransactions + " ADD COLUMN ProjectName TEXT");
            }
        }

        private void InsertDefaultCategories(SQLiteDatabase db)
        {
            // 默认支出分类
            InsertCategory(db, "餐饮", TransactionType.Expense, "🍽️", "#FF5722");
            InsertCategory(db, "车费", TransactionType.Expense, "🚕", "#FFC107");
            InsertCategory(db, "住宿", TransactionType.Expense, "🏨", "#9C27B0");
            InsertCategory(db, "采购", TransactionType.Expense, "🛒", "#FF9800");
            InsertCategory(db, "办公用品", TransactionType.Expense, "📝", "#795548");
            InsertCategory(db, "设备采购", TransactionType.Expense, "💻", "#607D8B");
            InsertCategory(db, "房租水电", TransactionType.Expense, "🏠", "#4CAF50");
            InsertCategory(db, "员工工资", TransactionType.Expense, "💰", "#F44336");
            InsertCategory(db, "通讯费", TransactionType.Expense, "📱", "#2196F3");
            InsertCategory(db, "维修保养", TransactionType.Expense, "🔧", "#FF6F00");
            InsertCategory(db, "培训学习", TransactionType.Expense, "📚", "#3F51B5");
            InsertCategory(db, "招待费", TransactionType.Expense, "🍷", "#E91E63");
            InsertCategory(db, "快递物流", TransactionType.Expense, "📦", "#00BCD4");
            InsertCategory(db, "广告宣传", TransactionType.Expense, "📢", "#FF5252");
            InsertCategory(db, "其他支出", TransactionType.Expense, "💳", "#757575");

            // 默认收入分类
            InsertCategory(db, "项目收入", TransactionType.Income, "💼", "#4CAF50");
            InsertCategory(db, "服务费", TransactionType.Income, "🛠️", "#2196F3");
            InsertCategory(db, "产品销售", TransactionType.Income, "🛍️", "#FF9800");
            InsertCategory(db, "咨询收入", TransactionType.Income, "💡", "#9C27B0");
            InsertCategory(db, "投资收益", TransactionType.Income, "📈", "#00BCD4");
            InsertCategory(db, "退款退税", TransactionType.Income, "💵", "#8BC34A");
            InsertCategory(db, "其他收入", TransactionType.Income, "💰", "#009688");
        }

        private void InsertCategory(SQLiteDatabase db, string name, TransactionType type, 
            string icon, string color)
        {
            ContentValues values = new ContentValues();
            values.Put("Name", name);
            values.Put("Type", (int)type);
            values.Put("Icon", icon);
            values.Put("Color", color);
            values.Put("IsDefault", 1);
            values.Put("CreatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            
            db.Insert(TableCategories, null, values);
        }

        // 添加交易记录
        public long AddTransaction(Transaction transaction)
        {
            using var db = WritableDatabase;
            if (db == null) return -1;

            ContentValues values = new ContentValues();
            values.Put("Type", (int)transaction.Type);
            values.Put("Amount", (double)transaction.Amount);
            values.Put("CategoryId", transaction.CategoryId);
            values.Put("CategoryName", transaction.CategoryName);
            if (transaction.ProjectId.HasValue)
            {
                values.Put("ProjectId", transaction.ProjectId.Value);
                values.Put("ProjectName", transaction.ProjectName);
            }
            values.Put("Description", transaction.Description);
            values.Put("Date", transaction.Date.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("CreatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("Remark", transaction.Remark);

            return db.Insert(TableTransactions, null, values);
        }

        // 更新交易记录
        public int UpdateTransaction(Transaction transaction)
        {
            using var db = WritableDatabase;
            if (db == null) return 0;

            ContentValues values = new ContentValues();
            values.Put("Type", (int)transaction.Type);
            values.Put("Amount", (double)transaction.Amount);
            values.Put("CategoryId", transaction.CategoryId);
            values.Put("CategoryName", transaction.CategoryName);
            if (transaction.ProjectId.HasValue)
            {
                values.Put("ProjectId", transaction.ProjectId.Value);
                values.Put("ProjectName", transaction.ProjectName);
            }
            values.Put("Description", transaction.Description);
            values.Put("Date", transaction.Date.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("UpdatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("Remark", transaction.Remark);

            return db.Update(TableTransactions, values, "Id = ?", 
                new string[] { transaction.Id.ToString() });
        }

        // 删除交易记录
        public int DeleteTransaction(int id)
        {
            using var db = WritableDatabase;
            if (db == null) return 0;

            return db.Delete(TableTransactions, "Id = ?", new string[] { id.ToString() });
        }

        // 获取所有交易记录
        public List<Transaction> GetAllTransactions()
        {
            var transactions = new List<Transaction>();
            using var db = ReadableDatabase;
            if (db == null) return transactions;

            using var cursor = db.RawQuery(
                "SELECT * FROM " + TableTransactions + " ORDER BY Date DESC", null);

            if (cursor != null && cursor.MoveToFirst())
            {
                do
                {
                    var projectIdIndex = cursor.GetColumnIndex("ProjectId");
                    var projectNameIndex = cursor.GetColumnIndex("ProjectName");
                    
                    transactions.Add(new Transaction
                    {
                        Id = cursor.GetInt(cursor.GetColumnIndex("Id")),
                        Type = (TransactionType)cursor.GetInt(cursor.GetColumnIndex("Type")),
                        Amount = (decimal)cursor.GetDouble(cursor.GetColumnIndex("Amount")),
                        CategoryId = cursor.GetInt(cursor.GetColumnIndex("CategoryId")),
                        CategoryName = cursor.GetString(cursor.GetColumnIndex("CategoryName")) ?? "",
                        ProjectId = projectIdIndex >= 0 && !cursor.IsNull(projectIdIndex) ? cursor.GetInt(projectIdIndex) : null,
                        ProjectName = projectNameIndex >= 0 ? cursor.GetString(projectNameIndex) : null,
                        Description = cursor.GetString(cursor.GetColumnIndex("Description")) ?? "",
                        Date = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("Date")) ?? ""),
                        CreatedAt = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("CreatedAt")) ?? ""),
                        Remark = cursor.GetString(cursor.GetColumnIndex("Remark"))
                    });
                } while (cursor.MoveToNext());
            }

            return transactions;
        }

        // 获取指定日期范围的交易记录
        public List<Transaction> GetTransactionsByDateRange(DateTime startDate, DateTime endDate)
        {
            var transactions = new List<Transaction>();
            using var db = ReadableDatabase;
            if (db == null) return transactions;

            string query = "SELECT * FROM " + TableTransactions + 
                " WHERE Date BETWEEN ? AND ? ORDER BY Date DESC";
            
            using var cursor = db.RawQuery(query, new string[] 
            { 
                startDate.ToString("yyyy-MM-dd 00:00:00"),
                endDate.ToString("yyyy-MM-dd 23:59:59")
            });

            if (cursor != null && cursor.MoveToFirst())
            {
                do
                {
                    var projectIdIndex = cursor.GetColumnIndex("ProjectId");
                    var projectNameIndex = cursor.GetColumnIndex("ProjectName");
                    
                    transactions.Add(new Transaction
                    {
                        Id = cursor.GetInt(cursor.GetColumnIndex("Id")),
                        Type = (TransactionType)cursor.GetInt(cursor.GetColumnIndex("Type")),
                        Amount = (decimal)cursor.GetDouble(cursor.GetColumnIndex("Amount")),
                        CategoryId = cursor.GetInt(cursor.GetColumnIndex("CategoryId")),
                        CategoryName = cursor.GetString(cursor.GetColumnIndex("CategoryName")) ?? "",
                        ProjectId = projectIdIndex >= 0 && !cursor.IsNull(projectIdIndex) ? cursor.GetInt(projectIdIndex) : null,
                        ProjectName = projectNameIndex >= 0 ? cursor.GetString(projectNameIndex) : null,
                        Description = cursor.GetString(cursor.GetColumnIndex("Description")) ?? "",
                        Date = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("Date")) ?? ""),
                        CreatedAt = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("CreatedAt")) ?? ""),
                        Remark = cursor.GetString(cursor.GetColumnIndex("Remark"))
                    });
                } while (cursor.MoveToNext());
            }

            return transactions;
        }

        // 获取所有分类
        public List<Category> GetAllCategories()
        {
            var categories = new List<Category>();
            using var db = ReadableDatabase;
            if (db == null) return categories;

            using var cursor = db.RawQuery(
                "SELECT * FROM " + TableCategories + " ORDER BY Type, Name", null);

            if (cursor != null && cursor.MoveToFirst())
            {
                do
                {
                    categories.Add(new Category
                    {
                        Id = cursor.GetInt(cursor.GetColumnIndex("Id")),
                        Name = cursor.GetString(cursor.GetColumnIndex("Name")) ?? "",
                        Type = (TransactionType)cursor.GetInt(cursor.GetColumnIndex("Type")),
                        Icon = cursor.GetString(cursor.GetColumnIndex("Icon")) ?? "",
                        Color = cursor.GetString(cursor.GetColumnIndex("Color")) ?? "#2196F3",
                        IsDefault = cursor.GetInt(cursor.GetColumnIndex("IsDefault")) == 1,
                        CreatedAt = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("CreatedAt")) ?? "")
                    });
                } while (cursor.MoveToNext());
            }

            return categories;
        }

        // 根据类型获取分类
        public List<Category> GetCategoriesByType(TransactionType type)
        {
            return GetAllCategories().Where(c => c.Type == type).ToList();
        }

        // 添加分类
        public long AddCategory(Category category)
        {
            using var db = WritableDatabase;
            if (db == null) return -1;

            ContentValues values = new ContentValues();
            values.Put("Name", category.Name);
            values.Put("Type", (int)category.Type);
            values.Put("Icon", category.Icon);
            values.Put("Color", category.Color);
            values.Put("IsDefault", category.IsDefault ? 1 : 0);
            values.Put("CreatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            return db.Insert(TableCategories, null, values);
        }

        // 获取统计数据
        public (decimal totalIncome, decimal totalExpense, decimal balance) GetStatistics(
            DateTime? startDate = null, DateTime? endDate = null)
        {
            using var db = ReadableDatabase;
            if (db == null) return (0, 0, 0);

            string whereClause = "";
            List<string> args = new List<string>();

            if (startDate.HasValue && endDate.HasValue)
            {
                whereClause = " WHERE Date BETWEEN ? AND ?";
                args.Add(startDate.Value.ToString("yyyy-MM-dd 00:00:00"));
                args.Add(endDate.Value.ToString("yyyy-MM-dd 23:59:59"));
            }

            // 计算总收入
            string incomeQuery = "SELECT SUM(Amount) FROM " + TableTransactions + 
                whereClause + (whereClause == "" ? " WHERE" : " AND") + " Type = " + 
                (int)TransactionType.Income;
            using var incomeCursor = db.RawQuery(incomeQuery, args.ToArray());
            decimal totalIncome = 0;
            if (incomeCursor != null && incomeCursor.MoveToFirst())
            {
                totalIncome = (decimal)incomeCursor.GetDouble(0);
            }

            // 计算总支出
            string expenseQuery = "SELECT SUM(Amount) FROM " + TableTransactions + 
                whereClause + (whereClause == "" ? " WHERE" : " AND") + " Type = " + 
                (int)TransactionType.Expense;
            using var expenseCursor = db.RawQuery(expenseQuery, args.ToArray());
            decimal totalExpense = 0;
            if (expenseCursor != null && expenseCursor.MoveToFirst())
            {
                totalExpense = (decimal)expenseCursor.GetDouble(0);
            }

            return (totalIncome, totalExpense, totalIncome - totalExpense);
        }

        // ============ 项目管理相关方法 ============
        
        // 添加项目
        public long AddProject(Project project)
        {
            using var db = WritableDatabase;
            if (db == null) return -1;

            ContentValues values = new ContentValues();
            values.Put("Name", project.Name);
            values.Put("Description", project.Description);
            values.Put("ClientName", project.ClientName);
            if (project.Budget.HasValue)
                values.Put("Budget", (double)project.Budget.Value);
            values.Put("Status", (int)project.Status);
            values.Put("StartDate", project.StartDate.ToString("yyyy-MM-dd HH:mm:ss"));
            if (project.EndDate.HasValue)
                values.Put("EndDate", project.EndDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("CreatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("Remark", project.Remark);

            return db.Insert(TableProjects, null, values);
        }

        // 更新项目
        public int UpdateProject(Project project)
        {
            using var db = WritableDatabase;
            if (db == null) return 0;

            ContentValues values = new ContentValues();
            values.Put("Name", project.Name);
            values.Put("Description", project.Description);
            values.Put("ClientName", project.ClientName);
            if (project.Budget.HasValue)
                values.Put("Budget", (double)project.Budget.Value);
            values.Put("Status", (int)project.Status);
            values.Put("StartDate", project.StartDate.ToString("yyyy-MM-dd HH:mm:ss"));
            if (project.EndDate.HasValue)
                values.Put("EndDate", project.EndDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("UpdatedAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            values.Put("Remark", project.Remark);

            return db.Update(TableProjects, values, "Id = ?", 
                new string[] { project.Id.ToString() });
        }

        // 删除项目
        public int DeleteProject(int id)
        {
            using var db = WritableDatabase;
            if (db == null) return 0;

            // 解除关联的交易记录
            ContentValues values = new ContentValues();
            values.PutNull("ProjectId");
            values.PutNull("ProjectName");
            db.Update(TableTransactions, values, "ProjectId = ?", new string[] { id.ToString() });

            // 删除项目
            return db.Delete(TableProjects, "Id = ?", new string[] { id.ToString() });
        }

        // 获取所有项目
        public List<Project> GetAllProjects()
        {
            var projects = new List<Project>();
            using var db = ReadableDatabase;
            if (db == null) return projects;

            using var cursor = db.RawQuery(
                "SELECT * FROM " + TableProjects + " ORDER BY CreatedAt DESC", null);

            if (cursor != null && cursor.MoveToFirst())
            {
                do
                {
                    var budgetIndex = cursor.GetColumnIndex("Budget");
                    var endDateIndex = cursor.GetColumnIndex("EndDate");
                    
                    projects.Add(new Project
                    {
                        Id = cursor.GetInt(cursor.GetColumnIndex("Id")),
                        Name = cursor.GetString(cursor.GetColumnIndex("Name")) ?? "",
                        Description = cursor.GetString(cursor.GetColumnIndex("Description")) ?? "",
                        ClientName = cursor.GetString(cursor.GetColumnIndex("ClientName")),
                        Budget = budgetIndex >= 0 && !cursor.IsNull(budgetIndex) ? (decimal)cursor.GetDouble(budgetIndex) : null,
                        Status = (ProjectStatus)cursor.GetInt(cursor.GetColumnIndex("Status")),
                        StartDate = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("StartDate")) ?? ""),
                        EndDate = endDateIndex >= 0 && !cursor.IsNull(endDateIndex) ? DateTime.Parse(cursor.GetString(endDateIndex) ?? "") : null,
                        CreatedAt = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("CreatedAt")) ?? ""),
                        Remark = cursor.GetString(cursor.GetColumnIndex("Remark"))
                    });
                } while (cursor.MoveToNext());
            }

            return projects;
        }

        // 获取项目详细统计
        public (decimal totalIncome, decimal totalExpense, decimal balance, int transactionCount) 
            GetProjectStatistics(int projectId)
        {
            using var db = ReadableDatabase;
            if (db == null) return (0, 0, 0, 0);

            // 计算总收入
            string incomeQuery = "SELECT SUM(Amount) FROM " + TableTransactions + 
                " WHERE ProjectId = ? AND Type = " + (int)TransactionType.Income;
            using var incomeCursor = db.RawQuery(incomeQuery, new string[] { projectId.ToString() });
            decimal totalIncome = 0;
            if (incomeCursor != null && incomeCursor.MoveToFirst())
            {
                totalIncome = (decimal)incomeCursor.GetDouble(0);
            }

            // 计算总支出
            string expenseQuery = "SELECT SUM(Amount) FROM " + TableTransactions + 
                " WHERE ProjectId = ? AND Type = " + (int)TransactionType.Expense;
            using var expenseCursor = db.RawQuery(expenseQuery, new string[] { projectId.ToString() });
            decimal totalExpense = 0;
            if (expenseCursor != null && expenseCursor.MoveToFirst())
            {
                totalExpense = (decimal)expenseCursor.GetDouble(0);
            }

            // 计算交易记录数
            string countQuery = "SELECT COUNT(*) FROM " + TableTransactions + " WHERE ProjectId = ?";
            using var countCursor = db.RawQuery(countQuery, new string[] { projectId.ToString() });
            int transactionCount = 0;
            if (countCursor != null && countCursor.MoveToFirst())
            {
                transactionCount = countCursor.GetInt(0);
            }

            return (totalIncome, totalExpense, totalIncome - totalExpense, transactionCount);
        }

        // 获取项目的所有交易记录
        public List<Transaction> GetTransactionsByProject(int projectId)
        {
            var transactions = new List<Transaction>();
            using var db = ReadableDatabase;
            if (db == null) return transactions;

            string query = "SELECT * FROM " + TableTransactions + 
                " WHERE ProjectId = ? ORDER BY Date DESC";
            
            using var cursor = db.RawQuery(query, new string[] { projectId.ToString() });

            if (cursor != null && cursor.MoveToFirst())
            {
                do
                {
                    var projectIdIndex = cursor.GetColumnIndex("ProjectId");
                    var projectNameIndex = cursor.GetColumnIndex("ProjectName");
                    
                    transactions.Add(new Transaction
                    {
                        Id = cursor.GetInt(cursor.GetColumnIndex("Id")),
                        Type = (TransactionType)cursor.GetInt(cursor.GetColumnIndex("Type")),
                        Amount = (decimal)cursor.GetDouble(cursor.GetColumnIndex("Amount")),
                        CategoryId = cursor.GetInt(cursor.GetColumnIndex("CategoryId")),
                        CategoryName = cursor.GetString(cursor.GetColumnIndex("CategoryName")) ?? "",
                        ProjectId = projectIdIndex >= 0 && !cursor.IsNull(projectIdIndex) ? cursor.GetInt(projectIdIndex) : null,
                        ProjectName = projectNameIndex >= 0 ? cursor.GetString(projectNameIndex) : null,
                        Description = cursor.GetString(cursor.GetColumnIndex("Description")) ?? "",
                        Date = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("Date")) ?? ""),
                        CreatedAt = DateTime.Parse(cursor.GetString(cursor.GetColumnIndex("CreatedAt")) ?? ""),
                        Remark = cursor.GetString(cursor.GetColumnIndex("Remark"))
                    });
                } while (cursor.MoveToNext());
            }

            return transactions;
        }
    }
}

